import pymysql
from todo import ToDo


class Todolist():

    @staticmethod
    def sql():
        return pymysql.Connection(host='192.168.143.136',database='jj',user='root',password='root@123ABC',autocommit=True)
    def create(self,title):
        with self.sql() as conn:
            cursor = conn.cursor()
            cursor.execute('insert into todos(title) values(%s)',(title,))
            cursor.execute('select last_insert_id()')
            (todo_id,)=cursor.fetchone()
            return ToDo(title,id=todo_id)
    def init_db(self):
        with self.sql() as conn:
            table_sql = """
            CREATE TABLE IF NOT EXISTS todos(
            todo_id int  NOT NULL PRIMARY KEY AUTO_INCREMENT,
            title VARCHAR(255) NOT NULL,
            completed int NOT NULL DEFAULT 0
)
            """
            curses=conn.cursor()
            curses.execute(table_sql)
    def list_todos(self):
        with self.sql() as conn:
            curses=conn.cursor()
            curses.execute('select * from todos')
            todos = curses.fetchall()
            list = []
            for row in todos:
                list.append(self.convert_row_to_object(row))
            return list
    def convert_row_to_object(self,row):
        return ToDo(row[1],id=row[0],completed=True if row[2] == 1 else False)

    def get_todo_byid(self, id):
        with self.sql() as conn:
            cursor = conn.cursor()
            cursor.execute('select * from todos where todo_id=%s',(id,))
            todo = cursor.fetchone()
            if todo is not None:
                return self.convert_row_to_object(todo)
    def mark_completed(self,id):
        with self.sql() as conn:
            curses = conn.cursor()
            curses.execute('update todos set completed = 1 where todo_id=%s',(id,))
            return self.get_todo_byid(id)
    def delete(self,id):
        a = self.get_todo_byid(id)
        with self.sql() as conn:
            curses = conn.cursor()
            curses.execute('delete from todos where todo_id=%s',(id,))
            return a

